package com.agency8x.model;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import com.agency8x.model.base.ColumnInfo;
import com.agency8x.model.base.TableInfo;

public class ModelBuilderModel {
	public static final ModelBuilderModel dao=new ModelBuilderModel();
	private Connection conn;
	//取得连接  
    private Connection GetConn(String jdbcUrl, String sUser, String sPwd,boolean newConnection) {
    	
        if(conn!=null && !newConnection) return conn;  
        
        String[] jdbcUrlParams=jdbcUrl.split(":");
        if(jdbcUrlParams.length<2){
        	return null;
        }
        String driverName=jdbcUrlParams[1].toLowerCase(); // jdbcUrl中的驱动名称
        
        Map<String,String> driverNames=new HashMap<String,String>();
        driverNames.put("mysql", "com.mysql.jdbc.Driver"); // "jdbc:mysql://127.0.0.1:3306/database"
        driverNames.put("sqlserver", "com.microsoft.sqlserver.jdbc.SQLServerDriver"); //  "jdbc:microsoft:sqlserver://MyDbComputerNameOrIP:1433;databaseName=master"
        driverNames.put("oracle", "oracle.jdbc.driver.OracleDriver"); // "jdbc:oracle:thin:@localhost:1521:orcl"; //orcl为数据库的SID
        driverNames.put("postgresql","org.postgresql.Driver"); // "jdbc:postgresql://localhost/myDB" //myDB为数据库名
        if(!driverNames.containsKey(driverName)){
        	return null;
        }
        try {
            Class.forName(driverNames.get(driverName));  
            conn = DriverManager.getConnection(jdbcUrl, sUser, sPwd);  
  
        } catch (Exception ex) {  
            // ex.printStackTrace();  
            System.out.println(ex.getMessage());  
            return null;  
        }  
        return conn;  
    }  
	/**
	  * 取得一张表的结构信息
	  * 使用DatabaseMetaData与ResultSetMetaData结合的方式获取全部属性
	  * @param conn   数据连接
	  * @param tableName    表名
	  * @return  表结构中列的存储对象
	  * @throws SQLException
	  */
	 public TableInfo getTableInfo(Connection conn,Statement st, String tableName) throws SQLException{
	  TableInfo result = new TableInfo();
	  /**设置表名*/
	  result.setTableName(tableName);
	  DatabaseMetaData dbmd = conn.getMetaData();
	  ResultSet rs = dbmd.getColumns(null, null, tableName.toUpperCase(), null);
	  /**判断字段是否自增*/
	  String sql = "select * from " + tableName + " where 1=2";
	  ResultSet rst = conn.prepareStatement(sql).executeQuery();
	  ResultSetMetaData rsmd = rst.getMetaData();
	  int i=1;
	  
	  while(rs.next()){
	   //列名称
	   String columnName = rs.getString("COLUMN_NAME").toLowerCase();//列名
	   //数据类型
	   int dataType = rs.getInt("DATA_TYPE");//类型  
	   //数据类型名称
	   String dataTypeName = rs.getString("TYPE_NAME").toLowerCase();   
	   //精度,列的大小
	   int precision = rs.getInt("COLUMN_SIZE");//精度   
	   //小数位数
	   int scale = rs.getInt("DECIMAL_DIGITS");// 小数的位数   
	   //是否为空
	   int isNull = rs.getInt("NULLABLE");//是否为空   
	   //字段默认值
	   String defaultValue = rs.getString("COLUMN_DEF");
	   //是否自增
	   boolean isAutoIncrement = rsmd.isAutoIncrement(i); //自增
	   
	   ColumnInfo col = new ColumnInfo();                     
	   col.setName(columnName);
	   col.setDataType(dataType);
	   col.setDataTypeName(dataTypeName);
	   col.setPrecision(precision);
	   col.setScale(scale);
	   col.setIsNull(isNull); 
	   col.setDefaultValue(defaultValue);
	   col.setAutoIncrement(isAutoIncrement);
	   
	   result.setColInfo(columnName, col);
	   i++;
	  }
	  rs.close();
	  /**设置主键*/
	  rs = dbmd.getPrimaryKeys(null, null, tableName);
	  while(rs.next()){
	   result.setPrimaryKey(rs.getString("COLUMN_NAME").toLowerCase(), true);
	   System.out.println(rs.getString("COLUMN_NAME"));
	  }
	  rs.close();
	  return result;
	 }
}
